package com.xm.dao.reception_desk;

import com.xm.entity.Category;
import com.xm.entity.Class;
import com.xm.entity.Coupons;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 优惠卷Service
 * @author 康文程
 * @date 2024-1-7 下午 11:47
 */
public class CouponsDao extends BaseUtil {
    /**
     * 根据多个商品类别Id和用户信息Id查询当前用户可用的优惠券
     *
     * @param categoryIds   多个商品类别Id，以逗号分隔
     * @param informationId 用户信息Id
     * @return List<Coupons> 可用的优惠券列表
     * @throws SQLException 抛出错误
     */
    public List<Coupons> getAvailableCouponsByCategoryIdsAndInformationId(String categoryIds, Integer informationId) throws SQLException {
        @Language("sql")
        String sql = "SELECT * FROM coupons WHERE (information_id = ? AND coupon_status <> 2 or coupon_range = 'all')";
        List<Object> params = new ArrayList<>();
        params.add(informationId);

        String separate = ",";
        if (categoryIds.contains(separate)) {
            // 如果categoryIds包含逗号，说明有多个id，需要拼接OR条件
            String[] ids = categoryIds.split(separate);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ids.length; i++) {
                if (i > 0) {
                    sb.append(" OR ");
                }
                sb.append("FIND_IN_SET(?, coupon_range) <> 0");
                params.add(ids[i]);
            }
            sql += " AND (" + sb + ")";
        } else {
            // 如果categoryIds只有一个id，直接添加参数即可
            sql += " AND FIND_IN_SET(?, coupon_range) <> 0";
            params.add(categoryIds);
        }

        // 添加优惠券有效期的查询条件
        sql += " AND coupon_valid_time <= NOW() AND coupon_end_time >= NOW()";

        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, params.toArray());

            List<Coupons> couponsList = new ArrayList<>();
            while (resultSet.next()) {
                Coupons coupons = new Coupons();
                coupons.setCouponId(resultSet.getInt("coupon_id"));
                coupons.setCouponTitle(resultSet.getString("coupon_title"));
                coupons.setCouponRange(resultSet.getString("coupon_range"));
                coupons.setCouponValidTime(resultSet.getTimestamp("coupon_valid_time"));
                coupons.setCouponEndTime(resultSet.getTimestamp("coupon_end_time"));
                coupons.setCouponStatus(resultSet.getInt("coupon_status"));
                coupons.setInformationId(resultSet.getInt("information_id"));
                coupons.setDiscount(resultSet.getBigDecimal("discount"));

                couponsList.add(coupons);
            }
            return couponsList;
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 根据用户信息Id查询当前用户可用的优惠券
     * @param informationId 用户信息Id
     * @return List<Coupons> 可用的优惠券列表
     * @throws SQLException 抛出错误
     */
    public List<Coupons> getAvailableCouponsByInformationId(Integer informationId,int couponStatus) throws SQLException {
         @Language("sql")
        String sql = "SELECT co.*,ca.category_name FROM coupons co" +
                "         join category ca on ca.category_id=co.coupon_range" +
                "         WHERE information_id = ? and coupon_status="+couponStatus;
        List<Object> params = new ArrayList<>();
        params.add(informationId);
        if(couponStatus==0){
            // 添加优惠券有效期的查询条件
            sql += " AND coupon_valid_time <= NOW() AND coupon_end_time >= NOW()";
        }

        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, params.toArray());
            List<Coupons> couponsList = new ArrayList<>();
            while (resultSet.next()) {
                Coupons coupons = new Coupons(
                        resultSet.getInt("coupon_id"),
                        resultSet.getString("coupon_title"),
                        resultSet.getTimestamp("coupon_valid_time"),
                        resultSet.getTimestamp("coupon_end_time"),
                        resultSet.getInt("coupon_status"),
                        resultSet.getInt("information_id"),
                        resultSet.getBigDecimal("discount"),
                        new Category(resultSet.getString("category_name"))
                );
                couponsList.add(coupons);
            }
            return couponsList;
        } catch (SQLException e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }
    /**
     * 根据用户信息Id查询当前用户可用的优惠券数量
     * @param informationId 用户信息Id
     * @return int 优惠券数量
     */
    public int couponsCount(Integer informationId,int couponStatus) throws SqlErrorException {
        @Language("sql")
        String sql="select count(coupon_id) from coupons WHERE information_id = ? and coupon_status="+couponStatus;
        ResultSet rs=executeQuery(sql,informationId);
        try {
            rs.next();
            return rs.getInt(1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }
    }

    /**
     * 根据优惠券ID修改coupon_status字段
     *
     * @param couponId 优惠券ID
     * @param couponStatus 优惠券状态
     * @return 成功返回true，失败返回false
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateCouponStatusById(Integer couponId, Integer couponStatus) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE coupons SET coupon_status = ? WHERE coupon_id = ?";
        Object[] params = {couponStatus, couponId};
        int result = executeUpdate(sql, params);
        return result == 1;
    }
}
